/*
This Procedure Changes the Purchase Order Status based on the Status of Line Items for a PO.
Author - Phani Raja Kumar
Date   - 21st Feb 05
Usage exec sp_change_purchase_order_status '6'
*/

ALTER      PROC sp_change_purchase_order_status @PO_ID varchar(17)
AS

BEGIN

	DECLARE @no_line_items INT				--Represents No. of PO Line Items
	DECLARE @no_line_items_with_new_status INT		--Represents No. of PO Line Items in New Status
	DECLARE @no_line_items_with_approved_status INT		--Represents No. of PO Line Items in Approved Status
	DECLARE @no_line_items_with_received_status INT		--Represents No. of PO Line Items in Received Status
	DECLARE @no_line_items_with_closed_status INT		--Represents No. of PO Line Items in Closed Status
	DECLARE @no_line_items_with_Cancelled_status INT	--Represents No. of PO Line Items in Cancelled Status
	DECLARE @po_status VARCHAR(50)
	DECLARE @PURCHASEORDER_TYPE INT

	SET @po_status = 'New'
	SELECT @PURCHASEORDER_TYPE = PURCHASEORDER_TYPE FROM PurchaseOrder_Table WHERE PurchaseOrder_Id = @PO_ID
	IF ( @PURCHASEORDER_TYPE = 2 ) --STANDARD
	 BEGIN
	 	SELECT @no_line_items = count(*) FROM PurchaseOrder_Line_Item_Table WHERE PurchaseOrder_Id = @PO_ID
		SELECT @no_line_items_with_new_status = count(*) FROM PurchaseOrder_Line_Item_Table WHERE PurchaseOrder_Id = @PO_ID and Status = 'New'
		SELECT @no_line_items_with_approved_status = count(*) FROM PurchaseOrder_Line_Item_Table WHERE PurchaseOrder_Id = @PO_ID and Status = 'Approved'
		SELECT @no_line_items_with_received_status = count(*) FROM PurchaseOrder_Line_Item_Table WHERE PurchaseOrder_Id = @PO_ID and ( Status = 'Received' OR Status = 'Closed' )
		SELECT @no_line_items_with_closed_status = count(*) FROM PurchaseOrder_Line_Item_Table WHERE PurchaseOrder_Id = @PO_ID and Status = 'Closed'
		SELECT @no_line_items_with_cancelled_status = count(*) FROM PurchaseOrder_Line_Item_Table WHERE PurchaseOrder_Id = @PO_ID and Status = 'Cancelled'
	
		IF @no_line_items = @no_line_items_with_Cancelled_status
		BEGIN
			SET @po_status = 'Cancelled'
		END
		ELSE IF @no_line_items = @no_line_items_with_closed_status + @no_line_items_with_Cancelled_status
		BEGIN
			SET @po_status = 'Closed'
		END
		ELSE IF @no_line_items_with_received_status > 0
		BEGIN
			SET @po_status = 'Received'
		END
		ELSE IF @no_line_items_with_approved_status > 0
		BEGIN
			SET @po_status = 'Approved'
		END
		ELSE IF @no_line_items = @no_line_items_with_new_status + @no_line_items_with_cancelled_status
		BEGIN
			SET @po_status = 'New'
		END
	 	
		
	 END
	ELSE IF ( @PURCHASEORDER_TYPE = 1 ) --OPEN
	 BEGIN
		/*
		This Part is to Close the Open Purchase Order If The Max $ Limit is Reached
		*/
	 
		DECLARE @maxAmt NUMERIC(18,2)
		DECLARE @issuedAmt NUMERIC(18,2)
		--SELECT @openPurchaseOrder_Id = OpenPO_Id FROM PurchaseOrder_Table WHERE PurchaseOrder_ID = @PO_ID		
		SELECT @maxAmt = PO_Max_Value FROM PurchaseOrder_Table WHERE PurchaseOrder_ID = @PO_ID
		--SELECT @issuedAmt = ISNULL( SUM ( PO_Total ), 0 ) FROM PurchaseOrder_Table WHERE OpenPO_Id = @openPurchaseOrder_Id
		SELECT @issuedAmt = ISNULL( SUM(PR_TOTAL), 0 ) FROM PurchaseOrder_Receipt_Table WHERE PURCHASEORDER_ID = @PO_ID

		IF @issuedAmt >= @maxAmt
		BEGIN
			SET @po_status =  'Closed'
		END
		ELSE IF @issuedAmt > 0
		BEGIN
			SET @po_status =  'Received'
		END
		ELSE
		BEGIN
			SET @po_status =  'Approved'				
		END
	 END
	
	UPDATE PurchaseOrder_Table SET PO_Status = @po_status WHERE PURCHASEORDER_ID = @PO_ID
END
